Relational and object-relational database management systems store information in tables of rows in a database. To retrieve data, queries that request data are submitted to a database server, which computes the queries and returns the data requested.
Query statements submitted to the database server should conform to the syntactical rules of a particular query language. One popular query language, known as the Structured Query Language (SQL), provides users a variety of ways to specify information to be retrieved.
A query that is submitted to a database server is analyzed by a query optimizer. Based on the analysis, the query optimizer generates an execution plan optimized for efficient execution. The optimized execution plan may be based on a rewrite of the query.
Many complex queries contain multiple subqueries that access the same tables and have the same join conditions. These subqueries are usually evaluated individually. However, the evaluation of these kinds of subqueries on an individual basis is inefficient because doing so results in redundant access of the same tables and redundant performance of the same join evaluations.
Therefore, it is desirable to develop techniques for rewriting queries with multiple subqueries to coalesce the multiple subqueries into a single subquery, thereby eliminating redundant table accesses and join evaluations.
The approaches described in this section are approaches that could be pursued, but not necessarily approaches that have been previously conceived or pursued. Therefore, unless otherwise indicated, it should not be assumed that any of the approaches described in this section qualify as prior art merely by virtue of their inclusion in this section.